package com.darco.darcoapp.db;


import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.TimeZone;
import java.util.Vector;

import com.darco.darcoapp.db.DarcoDataWorkerThreads.InsertCardThread;
import com.darco.darcoapp.db.DarcoDataWorkerThreads.InsertCategoryThread;
import com.darco.darcoapp.db.DarcoDataWorkerThreads.InsertUniqueCardThread;
import com.darco.darcoapp.db.DarcoDataWorkerThreads.UpdateCardImageInLocalDBThread;
import com.darco.darcoapp.db.DarcoDataWorkerThreads.insertCardListIntoLocalDBThread;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.util.Pair;
import static DARCOShared.utils.Constants.*;


public class DarcoData extends SQLiteOpenHelper{

	private static final String DB_NAME="darco.db";
	private static final int DB_VERSION=10;


	public DarcoData(Context ctx) {
		super(ctx, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		//CREATION of the TABLE ACCOUNT 
		db.execSQL("CREATE TABLE " + TABLE_NAME_ACCOUNTS +" ("
				+ ACCOUNT_ID		+" INTEGER, "
				+ ACCOUNT_EMAIL		+" TEXT PRIMARY KEY, "
				+ ACCOUNT_NAME 		+" TEXT, "
				+ ACCOUNT_SURNAME	+" TEXT, "
				+ ACCOUNT_USERNAME	+" TEXT, "
				+ ACCOUNT_PASSWORD	+" TEXT, "
				+ ACCOUNT_LASTLOC   +" TEXT, "
				+ ACCOUNT_CREATION  +" TEXT, "
				+ ACCOUNT_KEYPAIR  +" BLOB, "
				+ ACCOUNT_AVATAR    +" BLOB" +
				"); ");

		//CREATION of the TABLE CARDS 
		db.execSQL("CREATE TABLE " + TABLE_NAME_CARDS +" ("
				+ CARD_SN 		+" TEXT PRIMARY KEY, "
				+ CARD_NAME		+" TEXT, "
				+ CARD_STATS	+" TEXT, "
				+ CARD_SPECIAL	+" TEXT, "
				+ CARD_CATEGORY	+" INTEGER, "
				+ CARD_SUBCATEGORY  +" INTEGER, "
				+ CARD_PREVIEW		+" BLOB, "
				+ CARD_IMAGE		+" BLOB "
				+"); ");

		db.execSQL("CREATE TABLE " + TABLE_NAME_UNIQUECARDS +" ("
				+ UNIQUECARD_ID		+" TEXT, "
				+ UNIQUECARD_SN 		+" TEXT, "
				+ UNIQUECARD_ACTUALOWNER  +" TEXT, " 
				+ UNIQUECARD_AUTENTICATED	+" INT, " 
				+ UNIQUECARD_FIRSTAUTH	+" TEXT, "
				+ UNIQUECARD_FIRSTOWNER	+" TEXT, "
				+"PRIMARY KEY ( "+UNIQUECARD_ID+" , "+UNIQUECARD_SN+
				")); ");

		//CREATION of the TABLE CATEGORY 		
		db.execSQL("CREATE TABLE " + TABLE_NAME_CATEGORIES +" ("
				+ CATEGORY_ID		+" INTEGER PRIMARY KEY, "
				+ CATEGORY_NAME		+" TEXT, "
				+ CATEGORY_ALLOWSUB	+" INTEGER "
				+"); ");


		db.execSQL("CREATE TABLE " + TABLE_NAME_SYSTEM +" ("
				+ SYSTEM_APPCODE	+" TEXT PRIMARY KEY, "
				+ SYSTEM_SAVEDACCOUNT	+" TEXT, "
				+ SYSTEM_FIRSTSTART		+" INTEGER, "
				+ SYSTEM_ALLOWLOCATION	+" INTEGER"+
				"); ");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME_ACCOUNTS);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME_SYSTEM);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME_CATEGORIES);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME_CARDS);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME_UNIQUECARDS);
		onCreate(db);		
	}

	public boolean addSystem(String app_code, String saved_account, int first_start,
			int allow_location){
		try{
			deleteAll(TABLE_NAME_SYSTEM);
			SQLiteDatabase db= getWritableDatabase();
			//devo cancellare cio che c'era prima (altrimenti conflitti)

			ContentValues values= new ContentValues();
			values.put(SYSTEM_APPCODE, app_code);
			values.put(SYSTEM_SAVEDACCOUNT,saved_account);
			values.put(SYSTEM_FIRSTSTART,first_start);
			values.put(SYSTEM_ALLOWLOCATION,allow_location);
			db.insertOrThrow(TABLE_NAME_SYSTEM, null, values);
			db.close();
			close();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return false;}
	}



	public boolean addAccount(int id, String email, String name, String surname,
			String username, String password, String last_location, String creation, byte[] keyPair,
			byte[] avatar){
		try{
			deleteAll(TABLE_NAME_ACCOUNTS);
			SQLiteDatabase db= getWritableDatabase();

			ContentValues values= new ContentValues();
			values.put(ACCOUNT_ID, id);
			values.put(ACCOUNT_EMAIL,email);
			values.put(ACCOUNT_NAME,name);
			values.put(ACCOUNT_SURNAME,surname);
			values.put(ACCOUNT_USERNAME,username);
			values.put(ACCOUNT_PASSWORD,password);
			values.put(ACCOUNT_LASTLOC,last_location);
			values.put(ACCOUNT_CREATION,creation);
			values.put(ACCOUNT_KEYPAIR, keyPair);
			values.put(ACCOUNT_AVATAR,avatar);
			//cancello l'account immesso in precedenza	
			db.insertOrThrow(TABLE_NAME_ACCOUNTS, null, values);

			db.close();
			close();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return false;}
	}

	public ContentValues getSystemData(){
		SQLiteDatabase db= getReadableDatabase(); 
		ContentValues systemData=null;
		Cursor cursor = db.query(TABLE_NAME_SYSTEM, SYSTEM_FIELDS, null, null, null, null, null);
		if(cursor!=null){
			//startManagingCursor(cursor);				
			if(cursor.moveToFirst()){	//i found the data & i save them in account
				systemData= new  ContentValues();
				systemData.put(SYSTEM_SAVEDACCOUNT, cursor.getString(cursor.getColumnIndex(SYSTEM_SAVEDACCOUNT)));
				systemData.put(SYSTEM_ALLOWLOCATION, cursor.getInt(cursor.getColumnIndex(SYSTEM_ALLOWLOCATION)));
				systemData.put(SYSTEM_FIRSTSTART, cursor.getInt(cursor.getColumnIndex(SYSTEM_FIRSTSTART)));
				systemData.put(SYSTEM_APPCODE, cursor.getInt(cursor.getColumnIndex(SYSTEM_APPCODE)));
			}
			cursor.close();
		}
		db.close();
		close();
		return systemData;

	}

	public ContentValues getAccountInfo(){
		SQLiteDatabase db= getReadableDatabase();
		ContentValues accountInfo=null;
		Cursor cursor= db.query(TABLE_NAME_ACCOUNTS, ACCOUNTS_FIELDS,null,null,null,null,null);		
		if (cursor!=null){
			if(cursor.moveToFirst()){	//i found the data in the database!
				accountInfo= new ContentValues();
				accountInfo.put(ACCOUNT_EMAIL, cursor.getString(cursor.getColumnIndex(ACCOUNT_EMAIL)));
				accountInfo.put(ACCOUNT_ID, cursor.getInt(cursor.getColumnIndex(ACCOUNT_ID)));
				accountInfo.put(ACCOUNT_NAME, cursor.getString(cursor.getColumnIndex(ACCOUNT_NAME)));
				accountInfo.put(ACCOUNT_SURNAME, cursor.getString(cursor.getColumnIndex(ACCOUNT_SURNAME)));
				accountInfo.put(ACCOUNT_USERNAME, cursor.getString(cursor.getColumnIndex(ACCOUNT_USERNAME)));
				accountInfo.put(ACCOUNT_LASTLOC, cursor.getString(cursor.getColumnIndex(ACCOUNT_LASTLOC)));
				accountInfo.put(ACCOUNT_PASSWORD, cursor.getString(cursor.getColumnIndex(ACCOUNT_PASSWORD)));
				accountInfo.put(ACCOUNT_CREATION, cursor.getString(cursor.getColumnIndex(ACCOUNT_CREATION)));
				accountInfo.put(ACCOUNT_KEYPAIR, cursor.getBlob(cursor.getColumnIndex(ACCOUNT_KEYPAIR)));
				accountInfo.put(ACCOUNT_AVATAR, cursor.getBlob(cursor.getColumnIndex(ACCOUNT_AVATAR)));		
			}
			cursor.close();
		}
		db.close();
		close();
		return accountInfo;
	}


	public static String formatDateTime(Context context,String timeToFormat) {

		String finalDateTime = "";          

		SimpleDateFormat iso8601Format = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Date date = null;
		if (timeToFormat != null) {
			try {
				date = iso8601Format.parse(timeToFormat);
			} catch (ParseException e) {
				date = null;
			}

			if (date != null) {
				long when = date.getTime();
				int flags = 0;
				flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
				flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
				flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
				flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

				finalDateTime = android.text.format.DateUtils.formatDateTime(context,
						when + TimeZone.getDefault().getOffset(when), flags);               
			}
		}
		return finalDateTime;
	}

	public void deleteAll(String table_to_delete){
		try{
			SQLiteDatabase db= getWritableDatabase();
			db.delete(table_to_delete, null, null);
			db.close();
			close();
			return;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return;}
	}

	public boolean insertUniqueCard(String sn,String id,int auth, String actual_owner,
			String first_auth, String first_owner){
		try{
			SQLiteDatabase db= getWritableDatabase();

			//inserimento dati carta
			ContentValues unique= new ContentValues();
			unique.put(UNIQUECARD_SN,sn);
			unique.put(UNIQUECARD_ID,id);
			unique.put(UNIQUECARD_AUTENTICATED,auth);
			unique.put(UNIQUECARD_ACTUALOWNER,actual_owner);
			unique.put(UNIQUECARD_FIRSTOWNER, first_owner);
			unique.put(UNIQUECARD_FIRSTAUTH, first_auth);
			db.insertOrThrow(TABLE_NAME_UNIQUECARDS, null, unique);
			db.close();
			close();
			return true;

		} catch(Exception e){
			Log.d("Database", "Unique Card "+sn+","+id +" already in the database ");
			//e.printStackTrace();
			close();
			return false;}

	}
	
	public void insertUniqueCardAsync(String sn,String id,int auth, String actual_owner, String first_auth, String first_owner){
		InsertUniqueCardThread t = new InsertUniqueCardThread(this, sn, id, auth, actual_owner, first_auth, first_owner);
		t.start();
	}

	public boolean insertCategory(int id,String name,int allowsub){
		try{
			SQLiteDatabase db= getWritableDatabase();

			//inserimento dati carta
			ContentValues cat= new ContentValues();
			cat.put(CATEGORY_ID,id);
			cat.put(CATEGORY_NAME,id);
			cat.put(CATEGORY_ALLOWSUB,allowsub);
			db.insertOrThrow(TABLE_NAME_CATEGORIES, null,cat);
			db.close();
			close();
			return true;

		} catch(Exception e){
			Log.d("Database", "Category "+name+" already in the database ");
			//e.printStackTrace();
			close();
			return false;}

	}
	
	public void insertCategoryAsync(int id,String name,int allowsub){
		InsertCategoryThread t = new InsertCategoryThread(this, id, name, allowsub);
		t.start();
	}

	public boolean insertCard(String sn, String name, String stats,
			String special, int cat, int sub, byte[] preview,byte[] image){
		try{
			SQLiteDatabase db= getWritableDatabase();

			//inserimento dati carta
			ContentValues card= new ContentValues();
			card.put(CARD_SN,sn);
			card.put(CARD_NAME,name);
			card.put(CARD_STATS,stats);
			card.put(CARD_SPECIAL,special);
			card.put(CARD_CATEGORY,cat);
			card.put(CARD_SUBCATEGORY,sub);
			card.put(CARD_PREVIEW,preview);
			card.put(CARD_IMAGE,image);
			db.insertOrThrow(TABLE_NAME_CARDS, null, card);
			db.close();
			close();
			return true;
		} catch(Exception e){
			Log.d("Database", "Card "+sn+" already in the database ");
			//e.printStackTrace();
			close();
			return false;}

	}
	
	public void insertCardAsync(String sn, String name, String stats,	String special, int cat, int sub, byte[] preview,byte[] image){
		InsertCardThread t = new InsertCardThread(this, sn, name, stats, special, cat, sub, preview, image);
		t.start();
	}
	

	public boolean updateCardImageInLocalDB(String sn, byte[] image){
		try{
			SQLiteDatabase db= getWritableDatabase();
			ContentValues img = new ContentValues();
			img.put(CARD_IMAGE, image);
			db.update(TABLE_NAME_CARDS,img,CARD_SN+"="+"\'"+sn+"\'",null);
			db.close();
			close();
			return true;
		} catch(Exception e){
			Log.d("Database", "Image of the Card "+sn+"already in the database ");
			//e.printStackTrace();
			close();
			return false;}

	}
	
	public void updateCardImageInLocalDBAsync(String sn, byte[] image){
		UpdateCardImageInLocalDBThread t = new UpdateCardImageInLocalDBThread(this, sn, image);
		t.start();
	}

	public ContentValues getCardFromLocalDB(String sn){ 

		try{
			SQLiteDatabase db= getReadableDatabase();
			ContentValues cardInfo= null;
			Cursor cursor= db.query(TABLE_NAME_CARDS, CARDS_FIELDS,CARD_SN+"="+"\'"+sn+"\'",null,null,null,null);
			if (cursor!=null){
				if(cursor.moveToFirst()){	//i found the data in the database!
					cardInfo= new ContentValues();
					cardInfo.put(CARD_SN, cursor.getString(cursor.getColumnIndex(CARD_SN)));
					cardInfo.put(CARD_NAME, cursor.getString(cursor.getColumnIndex(CARD_NAME)));
					cardInfo.put(CARD_CATEGORY, cursor.getInt(cursor.getColumnIndex(CARD_CATEGORY)));
					cardInfo.put(CARD_SUBCATEGORY, cursor.getInt(cursor.getColumnIndex(CARD_SUBCATEGORY)));
					cardInfo.put(CARD_PREVIEW, cursor.getBlob(cursor.getColumnIndex(CARD_PREVIEW)));
					cardInfo.put(CARD_SPECIAL, cursor.getString(cursor.getColumnIndex(CARD_SPECIAL)));
					cardInfo.put(CARD_STATS, cursor.getString(cursor.getColumnIndex(CARD_STATS)));		

					//prendo il nome della categoria
					Cursor cursorCat= db.query(TABLE_NAME_CATEGORIES, CATEGORIES_FIELDS,CATEGORY_ID+"="+"\'"+cardInfo.getAsInteger(CARD_CATEGORY)+"\'",
							null,null,null,	null);
					if (cursorCat!=null){
						if(cursorCat.moveToFirst()){	//i found the data in the database!
							cardInfo.put(CARD_CATEGORY_NAME, cursorCat.getString(cursorCat.getColumnIndex(CATEGORY_NAME)));										
						}
						cursorCat.close();
					}
					//prendo il nome della subcategoria
					Cursor cursorSub= db.query(TABLE_NAME_CATEGORIES, CATEGORIES_FIELDS,CATEGORY_ID+"="+"\'"+cardInfo.getAsInteger(CARD_SUBCATEGORY)+"\'",
							null,null,null,null);
					if (cursorSub!=null){
						if(cursorSub.moveToFirst()){	//i found the data in the database!
							cardInfo.put(CARD_SUBCATEGORY_NAME, cursorSub.getString(cursorSub.getColumnIndex(CATEGORY_NAME)));											
						}
						cursorSub.close();
					}
				}
				cursor.close();
			}

			db.close();
			close();
			return cardInfo;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return null;}
	}

	public byte[] getCardImageFromLocalDB(String sn){ 

		try{
			SQLiteDatabase db= getReadableDatabase();
			byte[] image=null;
			String s[]={CARD_IMAGE};
			Cursor cursor= db.query(TABLE_NAME_CARDS, s,CARD_SN+"="+"\'"+sn+"\'",null,null,null,null);		
			if (cursor!=null){
				if(cursor.moveToFirst()){	//i found the data in the database!
					image = cursor.getBlob(cursor.getColumnIndex(CARD_IMAGE));
				}
				cursor.close();
			}
			db.close();
			close();
			return image;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return null;}
	}

	public ContentValues getUniqueCardFromLocalDB(String sn, String id){ 
		try{
			SQLiteDatabase db= getReadableDatabase();
			ContentValues uniqueInfo=null;
			Cursor cursor= db.query(TABLE_NAME_UNIQUECARDS, UNIQUECARDS_FIELDS,
					UNIQUECARD_ID+"="+"\'"+id+"\'"+" and "+UNIQUECARD_SN+"=\'"+sn+"\'",null,null,null ,null);

			if (cursor!=null){
				if(cursor.moveToFirst()){	//i found the data in the database!
					uniqueInfo= new ContentValues();
					uniqueInfo.put(UNIQUECARD_ID, cursor.getString(cursor.getColumnIndex(UNIQUECARD_ID)));
					uniqueInfo.put(UNIQUECARD_SN, cursor.getString(cursor.getColumnIndex(UNIQUECARD_SN)));
					uniqueInfo.put(UNIQUECARD_FIRSTOWNER, cursor.getString(cursor.getColumnIndex(UNIQUECARD_FIRSTOWNER)));
					uniqueInfo.put(UNIQUECARD_FIRSTAUTH, cursor.getString(cursor.getColumnIndex(UNIQUECARD_FIRSTAUTH)));
					uniqueInfo.put(UNIQUECARD_AUTENTICATED, cursor.getInt(cursor.getColumnIndex(UNIQUECARD_AUTENTICATED)));
					uniqueInfo.put(UNIQUECARD_ACTUALOWNER, cursor.getString(cursor.getColumnIndex(UNIQUECARD_ACTUALOWNER)));
				}
				cursor.close();
			}
			db.close();
			close();
			return uniqueInfo;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return null;}
	}
	
	
	
	public boolean insertCardListIntoLocalDB(ArrayList<ContentValues> unique,ArrayList<ContentValues> card){
		boolean insertSuccess= false;
		
		deleteAll(TABLE_NAME_UNIQUECARDS);
		//SQLiteDatabase db= getReadableDatabase();
		for(int i=0;i<unique.size();i++){
			
			
			insertCardAsync(card.get(i).getAsString(CARD_SN), card.get(i).getAsString(CARD_NAME), 
					card.get(i).getAsString(CARD_STATS), card.get(i).getAsString(CARD_SPECIAL), card.get(i).getAsInteger(CARD_CATEGORY),
					card.get(i).getAsInteger(CARD_SUBCATEGORY), card.get(i).getAsByteArray(CARD_PREVIEW), null);
			
			insertUniqueCardAsync(unique.get(i).getAsString(UNIQUECARD_SN), unique.get(i).getAsString(UNIQUECARD_ID),
					unique.get(i).getAsInteger(UNIQUECARD_AUTENTICATED), unique.get(i).getAsString(UNIQUECARD_ACTUALOWNER), 
					unique.get(i).getAsString(UNIQUECARD_FIRSTAUTH), unique.get(i).getAsString(UNIQUECARD_FIRSTOWNER));
			
			insertCategoryAsync(card.get(i).getAsInteger(CARD_CATEGORY), card.get(i).getAsString(CARD_CATEGORY_NAME), 0);

			insertCategoryAsync(card.get(i).getAsInteger(CARD_SUBCATEGORY), card.get(i).getAsString(CARD_SUBCATEGORY_NAME), 1);
						
			insertSuccess=true;
		}
		//db.close();
		return insertSuccess;
		
	}

	public void insertCardListIntoLocalDBAsync(ArrayList<ContentValues> unique,ArrayList<ContentValues> card){
		insertCardListIntoLocalDBThread t = new insertCardListIntoLocalDBThread(this, unique, card);
		t.start();
	}


	public boolean deleteUniqueCard( String sn, String id){
		try{
			SQLiteDatabase db= getWritableDatabase();
			String condition=UNIQUECARD_ID+"="+"\'"+id+"\'"+" and "+UNIQUECARD_SN+"=\'"+sn+"\'";
			db.delete(TABLE_NAME_UNIQUECARDS, condition, null);
			db.close();
			close();
			return true;
		} catch(Exception e){
			e.printStackTrace();
			close();
			return false;}
	
	}
}


